﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Diagnostics;
using System.Data.SqlClient;

namespace HZP
{
    public static class DB
    {
        public static SqlConnection HZP
        {
            get
            {
                return new SqlConnection(@"data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=" + Environment.CurrentDirectory+
                    @"\data\HZP.mdf");
            }
        }
        public static SqlConnection CKGL
        {
            get
            {
                return new SqlConnection(@"data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=" + Environment.CurrentDirectory +
                    @"\data\CKGL.mdf");
            }
        }
    }

    public static class LinqExt
    {
        public static void ForEach<T>(this IEnumerable<T> list, Action<T, int> action)
        {
            var i = 0;
            foreach (T one in list)
            {
                action(one, i);
                i++;
            }
        }
        public static void ForEach<T>(this IEnumerable<T> list, Action<T> action)
        {
            foreach (T one in list)
            {
                action(one);
            }

        }
    }

    /// <summary>
    /// 为oracle的数据连接扩展执行查询、更新SQL的方法，及执行存储过程的方法
    /// </summary>
    public static class SqlConnectionExtenions
    {
        /// <summary>
        /// 影响行数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dt">待更新的表</param>
        /// <returns></returns>
        public static int Update(this SqlConnection conn, DataTable dt, string selstr)
        {
            int ret = 0;
            try
            {
                conn.Open();
                SqlDataAdapter oda = new SqlDataAdapter(selstr, conn);
                SqlCommandBuilder cb = new SqlCommandBuilder(oda);
                cb.GetDeleteCommand();
                cb.GetInsertCommand();
                cb.GetUpdateCommand();

                ret = oda.Update(dt);
                oda.Dispose();
                oda = null;
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
            return ret;
        }
        /// <summary>
        /// 返回datatable
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable Query(this SqlConnection conn, string sql, Array paras = null)
        {
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                SqlDataAdapter oda = new SqlDataAdapter(cmd);

                oda.Fill(dt);
                dt.TableName = "sheet";
                oda.Dispose();
                oda = null;
                cmd.Dispose();
                cmd = null;

            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
            return dt;
        }
        /// <summary>
        /// 返回单个值。没有数据行，返回null.如果是null,则返回DBNULL.VALUE
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object QueryOneValue(this SqlConnection conn, string sql, Array paras = null)
        {
            object o = new object();
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                o = cmd.ExecuteScalar();
                cmd.Dispose();
                cmd = null;

            }
            catch { throw; }
            finally
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
            return o;
        }
        /// <summary>
        /// 返回影响行数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Execute(this SqlConnection conn, string sql, Array paras = null)
        {
            int r = -1;
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                r = cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;

            }
            catch { throw; }
            finally
            {
                conn.Close(); conn.Dispose();
                conn = null;
            }
            return r;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="proName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int Procedure(this SqlConnection conn, string proName, Array paras = null)
        {
            int r = -1;
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = proName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                r = cmd.ExecuteNonQuery();
                cmd.Dispose();
                cmd = null;

            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close(); conn.Dispose();
                conn = null;
            }
            return r;
        }

        public static DataTable ProcedureReturnDt(this SqlConnection conn, string proName, Array paras = null)
        {
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = proName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }

                SqlDataAdapter oda = new SqlDataAdapter(cmd);
                oda.Fill(dt);

                oda.Dispose();
                oda = null;
                cmd.Dispose();
                cmd = null;

            }
            catch { throw; }
            finally
            {
                conn.Close(); conn.Dispose();
                conn = null;
            }
            return dt;
        }

        /// <summary>
        /// 过程可以返回多个datatable到一个ds中，所以增加这个方法
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="proName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataSet ProcedureReturnDs(this SqlConnection conn, string proName, Array paras = null)
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = proName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }

                SqlDataAdapter oda = new SqlDataAdapter(cmd);
                oda.Fill(ds);

                oda.Dispose();
                oda = null;
                cmd.Dispose();
                cmd = null;

            }
            catch { throw; }
            finally
            {
                conn.Close(); conn.Dispose();
                conn = null;
            }
            return ds;
        }
    }
}
